package DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import MySQL.Goods;
import DBConn.DBConn;

public class GoodsDaoImpl implements GoodsDao{
	//增加
	public void add(Goods g) {
		String ssql = "insert into goods values(?,?,?,?)";
		Connection conn = DBConn.open();
		try {
			PreparedStatement pstmt = conn.prepareStatement(ssql);
			pstmt.setString(1,g.getId());
			pstmt.setString(2,g.getName());
			pstmt.setInt(3,g.getPrice());
			pstmt.setString(4,g.getSize());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBConn.close(conn);
		}
	}

	//修改
	public void update(Goods g) {
		String ssql = "update goods set name=?,price=?,size=? where id=?";
		Connection conn = DBConn.open();
		try {
			PreparedStatement pstmt = conn.prepareStatement(ssql);
			pstmt.setString(1,g.getName());
			pstmt.setInt(2,g.getPrice());
			pstmt.setString(3,g.getSize());
			pstmt.setString(4,g.getId());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBConn.close(conn);
		}
	}

	//删除
	public void delete(String id) {
		String ssql = "delete from goods where id=?";
		Connection conn = DBConn.open();
		try {
			PreparedStatement pstmt = conn.prepareStatement(ssql);
			pstmt.setString(1,id);
			pstmt.executeUpdate();
		} catch (SQLException e){
			e.printStackTrace();
		}finally{
			DBConn.close(conn);
		}
	}

	//通过id得到商品
	public Goods getGoodsById(String id) {
		String ssql = "select * from goods where id=?";
		Connection conn = DBConn.open();
		try {
			PreparedStatement pstmt = conn.prepareStatement(ssql);
			pstmt.setString(1, id);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				String name = rs.getString(2);
				int price = rs.getInt(3);
				String size = rs.getString(4);
				Goods g = new Goods();
				g.setId(id);
				g.setName(name);
				g.setPrice(price);
				g.setSize(size);
				return g;
			}
		} catch (SQLException e){
			e.printStackTrace();
		}finally{
			DBConn.close(conn);
		}	
		return null;
	}

	//查询
	public String query() {
		String ssql = "select * from goods";
		Connection conn = DBConn.open();
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(ssql);
			String s ="";
			while(rs.next()){
				String id = rs.getString(1);
				String name = rs.getString(2);
				int price = rs.getInt(3);
				String size = rs.getString(4);
				s = s+id+"   "+name+"   "+price+"   "+size+"\n";
			}
				return s;
		}catch (SQLException e1){
			e1.printStackTrace();
		}finally{
			DBConn.close(conn);
		}
		return null;
	}

}
